package cn.edu.hit.action;

import java.io.OutputStream;
import java.sql.ResultSet;

import cn.edu.hit.db.DBTools;

import com.opensymphony.xwork2.ActionSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

public class ExportAction extends ActionSupport {
	/**
	 * 
	 */
	private HttpServletRequest request;
	private HttpServletResponse response;
	private HttpSession session;
	private static final long serialVersionUID = 1L;
	
	public ExportAction(){
		request = ServletActionContext.getRequest();
		response = ServletActionContext.getResponse();
		session = request.getSession();
		session.getServletContext();
	}
	
	@Override
	public String execute() throws Exception {
		// TODO Auto-generated method stub  
		System.out.println(request.getParameter("exportType"));
		return "success";
	}
	
	/**
	 * 导出选房信息或者剩余床位信息
	 * @return
	 * @throws Exception
	 */
	public String export() throws Exception {
		// TODO Auto-generated method stub  
		try{
			String username = (String)session.getAttribute("username");
			String identify = (String)session.getAttribute("identify");
			if(username==null || username.length()<0){
				return "logout";
			}else if(!identify.equals("admin")){
				return "nopermission";
			}else{
				String sql="select role from admin where id='"+username+"'";
				DBTools db = new DBTools();
				ResultSet rs = db.executeQuery(sql);
				if(rs.next()){
					String type = request.getParameter("exportType");
					if(type==null || (!type.equals("chooseInfo") && !type.equals("leftBedInfo"))){
						return "error";
					}
					boolean permission =false;
					String addSql="";
					if(rs.getInt("role")==0){
						permission = true;
					}else if(rs.getInt("role")==1){
						permission = true;
						if(type.equals("chooseInfo")){
							addSql=" WHERE room.department='A10'";
						}else if(type.equals("leftBedInfo")){
							addSql=" AND room.department='A10'";
						}
					}else if(rs.getInt("role")==2){
						permission = true;
						if(type.equals("chooseInfo")){
							addSql=" WHERE room.department='A17'";
						}else if(type.equals("leftBedInfo")){
							addSql=" AND room.department='A17'";
						}
					}
					if(permission){
						String filename="保存结果";
						if(type.equals("chooseInfo")){
							filename = "学生选房信息"+filename;
							sql = "SELECT student_room.*,room.department,room.floor FROM student_room"
								+" LEFT JOIN room ON room.room_number=student_room.room_number"+addSql;
						}else{
							filename = "剩余床位"+filename;
							sql = "SELECT bed.room_number,bed.bed_position_id,room.floor,room.department FROM bed"
								+" LEFT JOIN room ON room.room_number=bed.room_number"
								+" WHERE bed.isChoosen=0"+addSql;
						}
						rs = db.executeQuery(sql);
						filename = new String(filename.getBytes("utf8"),"iso8859-1");
						
						response.reset();
						response.setContentType("application/vnd.ms-excel;charset=utf-8");
						response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
						OutputStream output = response.getOutputStream();
						
						WritableWorkbook wwb = Workbook.createWorkbook(output);
						WritableSheet sheet = wwb.createSheet("学生选房信息结果", 0);
						
						int row=1;
						int col=0;
						Label label;
						//表头
						if(type.equals("chooseInfo")){
							label = new Label(col,0,"学号");
							sheet.addCell(label);
							col=1;
						}
						label = new Label(col,0,"房间号");
						sheet.addCell(label);
						label = new Label(col+1,0,"床位");
						sheet.addCell(label);
						label = new Label(col+2,0,"楼层");
						sheet.addCell(label);
						label = new Label(col+3,0,"公寓");
						sheet.addCell(label);
						//数据
						
						while(rs.next()){
							if(type.equals("chooseInfo")){
								label = new Label(0,row,rs.getString("student_number"));
								sheet.addCell(label);
							}
							label = new Label(col,row,rs.getString("room_number"));
							sheet.addCell(label);
							label = new Label(col+1,row,rs.getString("bed_position_id"));
							sheet.addCell(label);
							label = new Label(col+2,row,rs.getString("floor"));
							sheet.addCell(label);
							label = new Label(col+3,row,rs.getString("department"));
							sheet.addCell(label);
							row++;
						}
						wwb.write();
						wwb.close();
						output.flush();
						output.close();
					}else{
						return "nopermission";
					}
				}else{
					return "error";
				}
				rs.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return "success";
	}
	
	/**
	 * 导出学生统计信息
	 * @return
	 * @throws Exception
	 */
	public String exportStudent() throws Exception {
		// TODO Auto-generated method stub  
		try{
			String username = (String)session.getAttribute("username");
			String identify = (String)session.getAttribute("identify");
			if(username==null || username.length()<0){
				return "logout";
			}else if(!identify.equals("admin")){
				return "nopermission";
			}else{
				String sql="select role from admin where id='"+username+"'";
				DBTools db = new DBTools();
				ResultSet rs = db.executeQuery(sql);
				if(rs.next()){
					if(rs.getInt("role")==0){
						String filename="学生个人信息统计结果";
						sql = "SELECT * FROM student LEFT JOIN student_room on student_room.student_number=student.student_number WHERE isRegisted=1 ";
						rs = db.executeQuery(sql);
						filename = new String(filename.getBytes("utf8"),"iso8859-1");
						
						response.reset();
						response.setContentType("application/vnd.ms-excel;charset=utf-8");
						response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
						OutputStream output = response.getOutputStream();
						
						WritableWorkbook wwb = Workbook.createWorkbook(output);
						WritableSheet sheet = wwb.createSheet("学生选房信息结果", 0);
						
						int row=0;
						int col=0;
						Label label;
						//表头
						label = new Label(col,row,"学号");
						sheet.addCell(label);
						label = new Label(col+1,row,"姓名");
						sheet.addCell(label);
						label = new Label(col+2,row,"民族");
						sheet.addCell(label);
						label = new Label(col+3,row,"性别");
						sheet.addCell(label);
						label = new Label(col+4,row,"学院");
						sheet.addCell(label);
						label = new Label(col+5,row,"专业");
						sheet.addCell(label);
						label = new Label(col+6,row,"政治面貌");
						sheet.addCell(label);
						label = new Label(col+7,row,"手机");
						sheet.addCell(label);
						label = new Label(col+8,row,"公寓");
						sheet.addCell(label);
						label = new Label(col+9,row,"房间");
						sheet.addCell(label);
						label = new Label(col+10,row,"床位");
						sheet.addCell(label);
						row++;
						
						while(rs.next()){							
							label = new Label(col,row,rs.getString("student_number"));
							sheet.addCell(label);
							label = new Label(col+1,row,rs.getString("student_name"));
							sheet.addCell(label);
							label = new Label(col+2,row,rs.getString("nation"));
							sheet.addCell(label);
							label = new Label(col+3,row,rs.getString("sex"));
							sheet.addCell(label);
							label = new Label(col+4,row,rs.getString("college"));
							sheet.addCell(label);
							label = new Label(col+5,row,rs.getString("major"));
							sheet.addCell(label);
							label = new Label(col+6,row,rs.getString("political_aff"));
							sheet.addCell(label);
							label = new Label(col+7,row,rs.getString("phone"));
							sheet.addCell(label);
							
							String department="无";
							String room="无";
							String bed="无";
							room=rs.getString("room_number");
							if(room==null || room.equals(""))room="无";
							else{ 
								department=room.substring(0, 3);
								bed=rs.getString("bed_position_id");
							}
							label = new Label(col+8,row,department);
							sheet.addCell(label);
							label = new Label(col+9,row,room);
							sheet.addCell(label);
							label = new Label(col+10,row,bed);
							sheet.addCell(label);
							
							row++;
						}
						wwb.write();
						wwb.close();
						output.flush();
						output.close();
					}else{
						return "nopermission";
					}
				}else{
					return "error";
				}
				rs.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return "success";
	}
	
}
